package org.alive.ee.app.repository;

/**
 * ��ҳSQL�������ࡣ����<code>PageResultSetExtractor</code>ʹ�á�
 * 
 * @author �캣��
 * @since JDK1.4
 * @history 2009-6-11 �캣�� �½�
 * @see com.comtop.mmgd.component.jdbcspring.PageResultSetExtractor
 */
public class PageSQL {

	private PageSQL() {
	}

	/**
	 * ��ȡ�����ѯ��ҳSQL��ʹ��Oracle��ROWNUMʵ�֡�
	 * 
	 * @param querySql ִ�в�ѯ��SQL��䣬������Ϊnull���߳���Ϊ0
	 * @param pageId
	 * @param size
	 * @return ����˷�ҳ�߼���SQL��䣬StringBuffer
	 */
	public static StringBuffer getPagingSQL(String querySql, int pageId, int size) {
		StringBuffer sbSQL = new StringBuffer(512);
		/**
		 * if (sql == null || sql.length() == 0) { return sbSQL; }
		 */
		int maxRow = pageId * size;
		int minRow = (pageId - 1) * size + 1; // ����1,������SQL�����ʹ��>=

		sbSQL.append("SELECT * FROM ( SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (");
		sbSQL.append(querySql);
		sbSQL.append(")INNER_TABLE WHERE ROWNUM <= ").append(maxRow);
		sbSQL.append(") OUTER_TABLE WHERE OUTER_TABLE_ROWNUM >= ");
		sbSQL.append(minRow);
		return sbSQL;
	}

	/**
	 * �õ���ѯȫ����SQL��䣬Ҳ���Բ�ʹ�ô˷�������ʹ��ʹ��ԭ����SQL���
	 * 
	 * @param querySql
	 * @return
	 */
	public static StringBuffer getPagingSQL(String querySql) {
		StringBuffer sbSQL = new StringBuffer(512);
		/**
		 * if (sql == null || sql.length() == 0) { return sbSQL; }
		 */
		int maxRow = Integer.MAX_VALUE;
		int minRow = 1;

		sbSQL.append("SELECT * FROM ( SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (");
		sbSQL.append(querySql);
		sbSQL.append(")INNER_TABLE WHERE ROWNUM <= ").append(maxRow);
		sbSQL.append(") OUTER_TABLE WHERE OUTER_TABLE_ROWNUM >= ");
		sbSQL.append(minRow);
		return sbSQL;
	}

	/**
	 * ��ȡ�����ѯ��ҳSQL��ʹ��Oracle��ROWNUMʵ�֡�
	 * 
	 * @param querySql ִ�в�ѯ��SQL��䣬������Ϊnull���߳���Ϊ0
	 * @param pageId
	 * @param size
	 * @return ����˷�ҳ�߼���SQL��䣬String
	 */
	public static String getPagingSQL(StringBuffer querySql, int pageId, int size) {
		StringBuffer sbSQL = new StringBuffer(512);
		/**
		 * if (sql == null || sql.length() == 0) { return sbSQL; }
		 */
		int maxRow = pageId * size;
		int minRow = (pageId - 1) * size + 1; // ����1,������SQL�����ʹ��>=

		sbSQL.append("SELECT * FROM ( SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (");
		sbSQL.append(querySql);
		sbSQL.append(")INNER_TABLE WHERE ROWNUM <= ").append(maxRow);
		sbSQL.append(") OUTER_TABLE WHERE OUTER_TABLE_ROWNUM >= ");
		sbSQL.append(minRow);
		return sbSQL.toString();
	}

	/**
	 * �õ���ѯȫ����SQL��䣬Ҳ���Բ�ʹ�ô˷�������ʹ��ʹ��ԭ����SQL��䣬����
	 * <code>EppdResultSetExtractor</code>��ȡ��ݡ�
	 * 
	 * @param querySql
	 * @return
	 */
	public static String getPagingSQL(StringBuffer querySql) {
		StringBuffer sbSQL = new StringBuffer(512);
		/**
		 * if (sql == null || sql.length() == 0) { return sbSQL; }
		 */
		int maxRow = Integer.MAX_VALUE;
		int minRow = 1;

		sbSQL.append("SELECT * FROM ( SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM FROM (");
		sbSQL.append(querySql);
		sbSQL.append(")INNER_TABLE WHERE ROWNUM <= ").append(maxRow);
		sbSQL.append(") OUTER_TABLE WHERE OUTER_TABLE_ROWNUM >= ");
		sbSQL.append(minRow);
		return sbSQL.toString();
	}

	// --------------------------------------------------------- For DB2

	/**
	 * Generate pagination SQL.
	 * 
	 * @param querySql logic query sql statement
	 * @param pageId page number to fetch data
	 * @param size rows each page
	 * @return
	 */
	public static StringBuffer getPageSQL(String querySql, int pageId, int size) {
		StringBuffer sbSQL = new StringBuffer(512);
		/**
		 * if (sql == null || sql.length() == 0) { return sbSQL; }
		 */
		int maxRow = pageId * size;
		int minRow = (pageId - 1) * size + 1; // plus 1, just for using ">=" in
												// SQL

		sbSQL.append("SELECT * FROM ( ");
		sbSQL.append("    SELECT ROW_NUMBER() OVER() AS RN, PAGETEMP.* FROM ( ");
		sbSQL.append(querySql);
		sbSQL.append("    ) PAGETEMP ");
		sbSQL.append(") WHERE RN >= ").append(minRow);
		sbSQL.append("  AND RN <= ");
		sbSQL.append(maxRow);
		return sbSQL;
	}

	/**
	 * Generate pagination SQL.
	 * 
	 * @param querySql logic query sql statement
	 * @param pageId page number to fetch data
	 * @param size rows each page
	 * @return
	 */
	public static String getPageSQL(StringBuffer querySql, int pageId, int size) {
		StringBuffer sbSQL = new StringBuffer(512);
		/**
		 * if (sql == null || sql.length() == 0) { return sbSQL; }
		 */
		int maxRow = pageId * size;
		int minRow = (pageId - 1) * size + 1; // plus 1, just for using ">=" in
												// SQL

		sbSQL.append("SELECT * FROM ( ");
		sbSQL.append("    SELECT ROW_NUMBER() OVER() AS RN, PAGETEMP.* FROM ( ");
		sbSQL.append(querySql);
		sbSQL.append("    ) PAGETEMP ");
		sbSQL.append(") WHERE RN >= ").append(minRow);
		sbSQL.append("  AND RN <= ");
		sbSQL.append(maxRow);
		return sbSQL.toString();
	}
}
